import datetime
from io import BytesIO

from flask import request, Response
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment

from tzq.document_management_system import dms_bp
from tzq.document_management_system.model.lookup_model import LookupClassify, LookupItem, LookupDelete, LookupDisable


@dms_bp.route('/api/exportLookupAllDataToExcel', methods=['GET', 'POST'])
def export_lookup_all_data_to_excel():
    # 导出 lookup 的所有数据
    # 入参
    principal = request.values.get("principal")
    online_date = request.values.get("online_date")
    created_by = request.values.get("created_by")

    # 定义Excel表头样式
    header_font = Font(name='Arial', bold=True, color="000000", size=12)
    header_fill = PatternFill(start_color="FF0000", end_color="FF0000", fill_type="solid")
    thin_border = Border(
        left=Side(style='thin'),
        right=Side(style='thin'),
        top=Side(style='thin'),
        bottom=Side(style='thin')
    )
    center_alignment = Alignment(horizontal="center", vertical="center")

    # 1、导出 lookup 的数据
    query = LookupClassify.query
    query = query.filter(LookupClassify.delete_flag == 'N')
    if principal: query = query.filter(LookupClassify.principal.ilike(f'%{principal}%'))
    if online_date: query = query.filter(LookupClassify.online_date == f'{online_date}')
    if created_by: query = query.filter(LookupClassify.created_by.ilike(f'%{created_by}%'))
    query = query.order_by(LookupClassify.online_date.desc(), LookupClassify.last_update_date.desc())
    query = query.paginate(page=1, per_page=1000000, error_out=False)
    wb = Workbook()
    ws = wb.active
    ws.title = "Lookup Classify"
    # 写入表头
    headers = [
        # 表头 - 业务字段
        'Code',
        'Parent Classify',
        'Name',
        'Status',
        'Lookup Type',
        'Desc',
        'Public Wirte',

        '责任人', '上线日期'
    ]
    ws.append(headers)
    # 为表头行（第1行）设置样式
    for col_num, header in enumerate(headers, 1):
        cell = ws.cell(row=1, column=col_num)
        cell.font = header_font
        cell.fill = header_fill
        cell.border = thin_border
        cell.alignment = center_alignment
    # 写入数据
    for item in query:
        ws.append([
            # 数据 - 业务字段
            item.classify_code,
            '',
            item.classify_name,
            '1',
            'COMMON',
            item.classify_desc,
            '1',

            item.principal,
            item.online_date.strftime('%Y-%m-%d')
        ])
    # 冻结首行（将 A2 设为冻结点，其上方的行会被冻结）
    ws.freeze_panes = 'A2'
    # 启用自动筛选：从 A1 到最后一列最后一行
    last_col_letter = ws.cell(row=1, column=ws.max_column).column_letter
    last_row = ws.max_row
    ws.auto_filter.ref = f"A1:{last_col_letter}{last_row}"
    # 调整列宽
    ws.column_dimensions['A'].width = 37  # Code
    ws.column_dimensions['B'].width = 18  # Parent Classify
    ws.column_dimensions['C'].width = 37  # Name
    ws.column_dimensions['D'].width = 10  # Status
    ws.column_dimensions['E'].width = 18  # Lookup Type
    ws.column_dimensions['F'].width = 37  # Desc
    ws.column_dimensions['G'].width = 18  # Public Wirte
    ws.column_dimensions['H'].width = 13  # 责任人
    ws.column_dimensions['I'].width = 13  # 上线日期
    # 设置某几列单元格居中显示
    for col in ['D', 'E', 'G', 'I']:
        for row in ws.iter_rows(min_col=ws[col + '1'].column, max_col=ws[col + '1'].column):
            for cell in row:
                cell.alignment = Alignment(horizontal='center', vertical='center')

    # 2、导出 lookup item 的数据
    query = LookupItem.query
    query = query.filter(LookupItem.delete_flag == 'N')
    if principal: query = query.filter(LookupItem.principal.ilike(f'%{principal}%'))
    if online_date: query = query.filter(LookupItem.online_date == f'{online_date}')
    if created_by: query = query.filter(LookupItem.created_by.ilike(f'%{created_by}%'))
    query = query.order_by(LookupItem.online_date.desc(), LookupItem.last_update_date.desc())
    query = query.paginate(page=1, per_page=1000000, error_out=False)
    # 创建第二个工作表
    ws2 = wb.create_sheet("Lookup Item")
    # 写入表头
    headers = [
        'Classify Code',
        'Item Code',
        'Item Name',
        'Desc',
        'Parent Item',
        'Status',
        'Lookup Type',
        'Language',
        'Sort',
        'Attribute1', 'Attribute2', 'Attribute3', 'Attribute4', 'Attribute5', 'Attribute6',

        '责任人', '上线日期'
    ]
    ws2.append(headers)
    # 为表头行（第1行）设置样式
    for col_num, header in enumerate(headers, 1):
        cell = ws2.cell(row=1, column=col_num)
        cell.font = header_font
        cell.fill = header_fill
        cell.border = thin_border
        cell.alignment = center_alignment
    # 写入数据
    for item in query:
        ws2.append([
            item.classify_code,
            item.item_code,
            item.item_name,
            item.desc,
            '',
            '1',
            'COMMON',
            item.language,
            item.sort,
            '', '', '', '', '', '',

            item.principal,
            item.online_date.strftime('%Y-%m-%d')
        ])
    # 冻结首行（将 A2 设为冻结点，其上方的行会被冻结）
    ws2.freeze_panes = 'A2'
    # 启用自动筛选：从 A1 到最后一列最后一行
    last_col_letter = ws2.cell(row=1, column=ws2.max_column).column_letter
    last_row = ws2.max_row
    ws2.auto_filter.ref = f"A1:{last_col_letter}{last_row}"
    # 调整列宽
    ws2.column_dimensions['A'].width = 37  # Classify Code
    ws2.column_dimensions['B'].width = 40  # Item Code
    ws2.column_dimensions['C'].width = 40  # Item Name
    ws2.column_dimensions['D'].width = 18  # Desc
    ws2.column_dimensions['E'].width = 18  # Parent Item
    ws2.column_dimensions['F'].width = 10  # Status
    ws2.column_dimensions['G'].width = 18  # Lookup Type
    ws2.column_dimensions['H'].width = 11  # Language
    ws2.column_dimensions['I'].width = 10  # Sort
    ws2.column_dimensions['J'].width = 10  # Attribute1
    ws2.column_dimensions['K'].width = 10  # Attribute2
    ws2.column_dimensions['L'].width = 10  # Attribute3
    ws2.column_dimensions['M'].width = 10  # Attribute4
    ws2.column_dimensions['N'].width = 10  # Attribute5
    ws2.column_dimensions['O'].width = 10  # Attribute6
    ws2.column_dimensions['P'].width = 13  # 责任人
    ws2.column_dimensions['Q'].width = 13  # 上线日期
    # 设置某几列单元格居中显示
    for col in ['E', 'F', 'G', 'H', 'I', 'Q']:
        for row in ws2.iter_rows(min_col=ws2[col + '1'].column, max_col=ws2[col + '1'].column):
            for cell in row:
                cell.alignment = Alignment(horizontal='center', vertical='center')

    # 3、Lookup手工删除
    query = LookupDelete.query
    query = query.filter(LookupDelete.delete_flag == 'N')
    if principal: query = query.filter(LookupDelete.principal.ilike(f'%{principal}%'))
    if online_date: query = query.filter(LookupDelete.online_date == f'{online_date}')
    if created_by: query = query.filter(LookupDelete.created_by.ilike(f'%{created_by}%'))
    query = query.order_by(LookupDelete.online_date.desc(), LookupDelete.last_update_date.desc())
    query = query.paginate(page=1, per_page=1000000, error_out=False)
    # 创建第三个工作表
    ws3 = wb.create_sheet("Lookup手工删除")
    # 写入表头
    headers = [
        'Classify Code',
        'Item Code',
        '操作',
        '责任人', '上线日期'
    ]
    ws3.append(headers)
    # 为表头行（第1行）设置样式
    for col_num, header in enumerate(headers, 1):
        cell = ws3.cell(row=1, column=col_num)
        cell.font = header_font
        cell.fill = header_fill
        cell.border = thin_border
        cell.alignment = center_alignment
    # 写入数据
    for item in query:
        ws3.append([
            item.classify_code,
            item.item_code,
            '手工删除',
            item.principal,
            item.online_date.strftime('%Y-%m-%d')
        ])
    # 冻结首行（将 A2 设为冻结点，其上方的行会被冻结）
    ws3.freeze_panes = 'A2'
    # 启用自动筛选：从 A1 到最后一列最后一行
    last_col_letter = ws3.cell(row=1, column=ws3.max_column).column_letter
    last_row = ws3.max_row
    ws3.auto_filter.ref = f"A1:{last_col_letter}{last_row}"
    # 调整列宽
    ws3.column_dimensions['A'].width = 37  # Classify Code
    ws3.column_dimensions['B'].width = 40  # Item Code
    ws3.column_dimensions['C'].width = 10  # 操作
    ws3.column_dimensions['D'].width = 13  # 责任人
    ws3.column_dimensions['E'].width = 13  # 上线日期
    # 设置某几列单元格居中显示
    for col in ['C', 'E']:
        for row in ws3.iter_rows(min_col=ws3[col + '1'].column, max_col=ws3[col + '1'].column):
            for cell in row:
                cell.alignment = Alignment(horizontal='center', vertical='center')

    # 4、Lookup手工失效
    query = LookupDisable.query
    query = query.filter(LookupDisable.delete_flag == 'N')
    if principal: query = query.filter(LookupDisable.principal.ilike(f'%{principal}%'))
    if online_date: query = query.filter(LookupDisable.online_date == f'{online_date}')
    if created_by: query = query.filter(LookupDisable.created_by.ilike(f'%{created_by}%'))
    query = query.order_by(LookupDisable.online_date.desc(), LookupDisable.last_update_date.desc())
    query = query.paginate(page=1, per_page=1000000, error_out=False)
    # 创建第四个工作表
    ws4 = wb.create_sheet("Lookup手工失效")
    # 写入表头
    headers = [
        'Classify Code',
        'Item Code',
        '操作',
        '责任人', '上线日期'
    ]
    ws4.append(headers)
    # 为表头行（第1行）设置样式
    for col_num, header in enumerate(headers, 1):
        cell = ws4.cell(row=1, column=col_num)
        cell.font = header_font
        cell.fill = header_fill
        cell.border = thin_border
        cell.alignment = center_alignment
    # 写入数据
    for item in query:
        ws4.append([
            item.classify_code,
            item.item_code,
            '手工失效',
            item.principal,
            item.online_date.strftime('%Y-%m-%d')
        ])
    # 冻结首行（将 A2 设为冻结点，其上方的行会被冻结）
    ws4.freeze_panes = 'A2'
    # 启用自动筛选：从 A1 到最后一列最后一行
    last_col_letter = ws4.cell(row=1, column=ws4.max_column).column_letter
    last_row = ws4.max_row
    ws4.auto_filter.ref = f"A1:{last_col_letter}{last_row}"
    # 调整列宽
    ws4.column_dimensions['A'].width = 37  # Classify Code
    ws4.column_dimensions['B'].width = 40  # Item Code
    ws4.column_dimensions['C'].width = 10  # 操作
    ws4.column_dimensions['D'].width = 13  # 责任人
    ws4.column_dimensions['E'].width = 13  # 上线日期
    # 设置某几列单元格居中显示
    for col in ['C', 'E']:
        for row in ws4.iter_rows(min_col=ws4[col + '1'].column, max_col=ws4[col + '1'].column):
            for cell in row:
                cell.alignment = Alignment(horizontal='center', vertical='center')

    date_string = datetime.datetime.now().strftime("%Y%m%d%H%M%S")

    # 将工作簿写入 BytesIO 流
    output = BytesIO()
    wb.save(output)
    output.seek(0)

    if online_date:
        version_string = "(v." + online_date + ")-"
    else:
        version_string = "ALL-"

    # 使用 Response 返回 Excel 文件
    return Response(
        output.getvalue(),
        mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
        headers={
            "Content-Disposition": "attachment; filename=jalor-saasLookup-" + version_string + date_string + ".xlsx"
            # 文件名不能为中文，不然报错了
        }
    )
